﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using Code;
using System.Data.Common;
using System.Data;

namespace E2_WEBSITE.Code.Product
{
    public class ProductDataAccess
    {
        protected DataProvider _provider = DataProvider.GlobalDataProvider;

        public String ConvertToSqlDateTime(DateTime dt)
        {
            int day;
            int mounth;
            int year;
            int hour;
            int minute;
            int second;

            day = dt.Day;
            mounth = dt.Month;
            year = dt.Year;
            hour = dt.Hour;
            minute = dt.Minute;
            second = dt.Second;

            String str = mounth.ToString() + "/" + day.ToString() + "/" + year.ToString() + " " + hour.ToString() + ":" + minute.ToString() + ":" + second.ToString();
            return str;
        }

        public bool UpdateProduct(ProductInfo productInfo)
        {
            try
            {
                String strSQL;
                if(productInfo.Khuyen_Mai != null)
                    strSQL = String.Format("Update SAN_PHAM " +
                                           "Set TenSanPham = '{0}', idNSX = {1}, MoTa = N'{2}', Gia = {3}, idKhuyenMai = {4}, " +
                                           "idNhaPhanPhoi = {5}, Diem = {6}, SoLuongTon = {7}, NgayThemSP = '{8}', " + 
                                           "BaoHanh = N'{9}', idLoaiSanPham = {10}, LoaiLinhKien = {11} Where id = {12}",
                                           productInfo.TenSanPham, productInfo.NSX.ID, productInfo.MoTa,
                                           productInfo.Gia, productInfo.Khuyen_Mai.ID, productInfo.Nha_Phan_Phoi.ID,
                                           productInfo.Diem, productInfo.SoLuongTon, ConvertToSqlDateTime(productInfo.NgayThemSP),
                                           productInfo.BaoHanh, productInfo.Loai_San_Pham.ID, productInfo.LoaiLinhKien, productInfo.ID);
                else
                    strSQL = String.Format("Update SAN_PHAM " +
                                           "Set TenSanPham = '{0}', idNSX = {1}, MoTa = '{2}', Gia = {3}, idKhuyenMai = null, " + 
                                           "idNhaPhanPhoi = {4}, Diem = {5}, SoLuongTon = {6}, NgayThemSP = '{7}', BaoHanh = '{8}', " +
                                           "idLoaiSanPham = {9}, LoaiLinhKien = {10} Where id = {11}",
                                           productInfo.TenSanPham, productInfo.NSX.ID, productInfo.MoTa, productInfo.Gia, 
                                           productInfo.Nha_Phan_Phoi.ID, productInfo.Diem, productInfo.SoLuongTon, 
                                           ConvertToSqlDateTime(productInfo.NgayThemSP), productInfo.BaoHanh, 
                                           productInfo.Loai_San_Pham.ID, productInfo.LoaiLinhKien, productInfo.ID);

                _provider.ExecuteNonQuery(strSQL);
                return true;
            }
            catch(Exception ex)
            {
                String str = ex.ToString();
                return false;
            }
        }

        public bool InsertProduct(ProductInfo productInfo)
        {
            try
            {
                String strSQL;
                if (productInfo.Khuyen_Mai != null)
                    strSQL = String.Format("Insert Into SAN_PHAM(TenSanPham,idNSX,MoTa,Gia,idKhuyenMai,idNhaPhanPhoi,Diem,SoLuongTon,NgayThemSP,BaoHanh,idLoaiSanPham, LoaiLinhKien) Values " +
                                           "('{0}',{1},N'{2}',{3},{4},{5},{6},{7},'{8}',N'{9}',{10},{11})",
                                           productInfo.TenSanPham, productInfo.NSX.ID, productInfo.MoTa,
                                           productInfo.Gia, productInfo.Khuyen_Mai.ID, productInfo.Nha_Phan_Phoi.ID,
                                           productInfo.Diem, productInfo.SoLuongTon, ConvertToSqlDateTime(productInfo.NgayThemSP),
                                           productInfo.BaoHanh, productInfo.Loai_San_Pham.ID, productInfo.LoaiLinhKien);
                else
                    strSQL = String.Format("Insert Into SAN_PHAM(TenSanPham,idNSX,MoTa,Gia,idNhaPhanPhoi,Diem,SoLuongTon,NgayThemSP,BaoHanh,idLoaiSanPham, LoaiLinhKien) Values " +
                                           "('{0}',{1},N'{2}',{3},{4},{5},{6},'{7}',N'{8}',{9},{10})",
                                           productInfo.TenSanPham, productInfo.NSX.ID, productInfo.MoTa,
                                           productInfo.Gia, productInfo.Nha_Phan_Phoi.ID, productInfo.Diem,
                                           productInfo.SoLuongTon, ConvertToSqlDateTime(productInfo.NgayThemSP),
                                           productInfo.BaoHanh, productInfo.Loai_San_Pham.ID, productInfo.LoaiLinhKien);

                _provider.ExecuteNonQuery(strSQL);
                return true;
            }
            catch (Exception ex)
            {
                String str = ex.ToString();
                return false;
            }
        }

        public String GetImageURLFromID(int id)
        {
            DataTable reader = null;
            try
            {
                String strSQL = String.Format("Select ha.URL as 'ImageURL' " +
                                              "From SAN_PHAM sp, HINH_ANH ha " +
                                              "Where sp.ID = ha.idSanPham and sp.ID = {0}", id);

                reader = _provider.ExecuteQueryDataTable(strSQL);

                return reader.Rows[0]["ImageURL"].ToString();
            }
            catch
            {
                return "";
            }
        }

        public DataTable GetSanPhamListWithNhaSanXuat(int id)
        {
            DataTable reader = null;
            try
            {
                String strSQL = String.Format("Select sp.ID, ha.Url as 'HinhAnh', sp.TenSanPham, sp.Gia, nsx.TenNSX, npp.TenNhaPhanPhoi, sp.NgayThemSP, sp.SoLuongTon " +
                                              "From SAN_PHAM sp,HINH_ANH ha,NHA_PHAN_PHOI npp, NHA_SAN_XUAT nsx " +
                                              "Where sp.Id = ha.idSanPham and sp.idNhaPhanPhoi = npp.id and sp.idNSX = nsx.id and nsx.id = {0}", id);
                reader = _provider.ExecuteQueryDataTable(strSQL);
                return reader;
            }
            catch
            {
                return null;
            }
        }

        public DataTable GetSanPhamListWithNhaPhanPhoi(int id)
        {
            DataTable reader = null;
            try
            {
                String strSQL = String.Format("Select sp.ID, ha.Url as 'HinhAnh', sp.TenSanPham, sp.Gia, nsx.TenNSX, npp.TenNhaPhanPhoi, sp.NgayThemSP, sp.SoLuongTon " +
                                              "From SAN_PHAM sp,HINH_ANH ha,NHA_PHAN_PHOI npp, NHA_SAN_XUAT nsx " +
                                              "Where sp.Id = ha.idSanPham and sp.idNhaPhanPhoi = npp.id and sp.idNSX = nsx.id and npp.id = {0}", id);
                reader = _provider.ExecuteQueryDataTable(strSQL);
                return reader;
            }
            catch
            {
                return null;
            }
        }

        public DataTable GetSanPhamListWithTenSanPham(String strTenSanPham)
        {
            DataTable reader = null;
            try
            {
                String strSQL = String.Format("Select sp.ID, ha.Url as 'HinhAnh', sp.TenSanPham, sp.Gia, nsx.TenNSX, npp.TenNhaPhanPhoi, sp.NgayThemSP, sp.SoLuongTon " +
                                              "From SAN_PHAM sp,HINH_ANH ha,NHA_PHAN_PHOI npp, NHA_SAN_XUAT nsx " +
                                              "Where sp.Id = ha.idSanPham and sp.idNhaPhanPhoi = npp.id and sp.idNSX = nsx.id and sp.TenSanPham like N'%{0}%'", strTenSanPham);
                reader = _provider.ExecuteQueryDataTable(strSQL);
                return reader;
            }
            catch
            {
                return null;
            }
        }

        public DataTable GetSanPhamListWithLoaiSanPham(int id)
        {
            DataTable reader = null;
            try
            {
                String strSQL = String.Format("Select sp.ID, ha.Url as 'HinhAnh', sp.TenSanPham, sp.Gia, nsx.TenNSX, npp.TenNhaPhanPhoi, sp.NgayThemSP, sp.SoLuongTon " +
                                              "From SAN_PHAM sp,HINH_ANH ha,NHA_PHAN_PHOI npp, NHA_SAN_XUAT nsx, LOAI_SAN_PHAM lsp " +
                                              "Where sp.Id = ha.idSanPham and sp.idNhaPhanPhoi = npp.id and sp.idNSX = nsx.id and sp.idLoaiSanPham = lsp.id and lsp.id = {0}", id);
                reader = _provider.ExecuteQueryDataTable(strSQL);
                return reader;
            }
            catch
            {
                return null;
            }
        }

        public DataTable GetSanPhamList()
        {
            DataTable reader = null;
            try
            {
                String strSQL = "Select sp.ID, ha.Url as 'HinhAnh', sp.TenSanPham, sp.Gia, nsx.TenNSX, npp.TenNhaPhanPhoi, sp.NgayThemSP, sp.SoLuongTon " +
                                "From SAN_PHAM sp,HINH_ANH ha,NHA_PHAN_PHOI npp, NHA_SAN_XUAT nsx " +
                                "Where sp.Id = ha.idSanPham and sp.idNhaPhanPhoi = npp.id and sp.idNSX = nsx.id";
                reader = _provider.ExecuteQueryDataTable(strSQL);
                return reader;
            }
            catch
            {
                return null;
            }
        }

        public ProductInfo GetTopLaptop()
        {
            DataTable reader = null;
            try
            {
                string strSQL = "SELECT TOP 1 SAN_PHAM.id, SAN_PHAM.TenSanPham, SAN_PHAM.Gia, HINH_ANH.Url, SAN_PHAM.idLoaiSanPham FROM SAN_PHAM INNER JOIN HINH_ANH ON SAN_PHAM.id = HINH_ANH.idSanPham WHERE SAN_PHAM.idLoaiSanPham = 17 ORDER BY SAN_PHAM.Diem DESC";

                reader = _provider.ExecuteQueryDataTable(strSQL);

                if (reader != null)
                {
                    ProductInfo tmpProduct = new ProductInfo();
                    foreach (DataRow row in reader.Rows)
                    {
                        HinhAnh tmpPic = new HinhAnh();
                        tmpPic.URL = "~//images//HinhAnhSanPham//" + row["Url"].ToString();
                        tmpProduct.Hinh_Anh = tmpPic;

                        LoaiSanPham tmpCategory = new LoaiSanPham();
                        tmpCategory.ID = int.Parse(row["idLoaiSanPham"].ToString());
                        tmpProduct.Loai_San_Pham = tmpCategory;

                        tmpProduct.TenSanPham = row["TenSanPham"].ToString();
                        tmpProduct.ID = int.Parse(row["id"].ToString());
                        tmpProduct.Gia = int.Parse(row["Gia"].ToString());                                                
                    }
                    return tmpProduct;
                }
                else
                    return null;
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
            finally
            {
                reader.Clear();
            }
        }

        public List<NhaSanXuat> GetNhaSanXuatList()
        {
            DataTable reader = null;
            try
            {
                String strSQL = "Select * From NHA_SAN_XUAT";
                reader = _provider.ExecuteQueryDataTable(strSQL);
                List<NhaSanXuat> nsxList = new List<NhaSanXuat>();
                foreach (DataRow row in reader.Rows)
                {
                    NhaSanXuat nsx = new NhaSanXuat();
                    nsx.ID = Convert.ToInt32(row["ID"].ToString());
                    nsx.TenNSX = row["TenNSX"].ToString();
                    nsx.QuocGia = row["QuocGia"].ToString();
                    nsxList.Add(nsx);
                }
                return nsxList;
            }
            catch
            {
                return null;
            }
        }

        public List<KhuyenMai> GetKhuyenMaiList()
        {
            DataTable reader = null;
            try
            {
                String strSQL = "Select * From KHUYEN_MAI";
                reader = _provider.ExecuteQueryDataTable(strSQL);
                List<KhuyenMai> kmList = new List<KhuyenMai>();
                foreach (DataRow row in reader.Rows)
                {
                    KhuyenMai km = new KhuyenMai();
                    km.ID = Convert.ToInt32(row["ID"].ToString());
                    km.HinhThuc = row["HinhThuc"].ToString();
                    km.PhanTramGiam = Convert.ToInt32(row["PhanTramGiam"].ToString());
                    kmList.Add(km);
                }
                return kmList;
            }
            catch
            {
                return null;
            }
        }

        public List<NhaPhanPhoi> GetNhaPhanPhoiList()
        {
            DataTable reader = null;
            try
            {
                String strSQL = "Select * From NHA_PHAN_PHOI";
                reader = _provider.ExecuteQueryDataTable(strSQL);
                List<NhaPhanPhoi> nppList = new List<NhaPhanPhoi>();

                foreach (DataRow row in reader.Rows)
                {
                    NhaPhanPhoi npp = new NhaPhanPhoi();
                    npp.ID = Convert.ToInt32(row["ID"].ToString());
                    npp.TenNhanPhanPhoi = row["TenNhaPhanPhoi"].ToString();
                    nppList.Add(npp);
                }

                return nppList;
            }
            catch
            {
                return null;
            }
        }

        public List<LoaiSanPham> GetLoaiSanPhamList()
        {
            DataTable reader = null;
            try
            {
                String strSQL = "Select * From LOAI_SAN_PHAM";
                reader = _provider.ExecuteQueryDataTable(strSQL);
                List<LoaiSanPham> lspList = new List<LoaiSanPham>();
                foreach (DataRow row in reader.Rows)
                {
                    LoaiSanPham lsp = new LoaiSanPham();
                    lsp.ID = Convert.ToInt32(row["ID"].ToString());
                    lsp.TenLoaiSanPham = row["TenLoaiSanPham"].ToString();
                    lspList.Add(lsp);
                }
                return lspList;
            }
            catch
            {
                return null;
            }
        }

        public List<ProductInfo> getTopBestSellProduct(int numProduct)
        {
            DataTable reader = null;
            try
            {
                string strSQL = "SELECT TOP " + numProduct.ToString() + " SAN_PHAM.id, SAN_PHAM.TenSanPham, SAN_PHAM.Gia, HINH_ANH.Url, SAN_PHAM.idLoaiSanPham FROM SAN_PHAM INNER JOIN HINH_ANH ON SAN_PHAM.id = HINH_ANH.idSanPham ORDER BY SAN_PHAM.SoLuongTon DESC";

                reader = _provider.ExecuteQueryDataTable(strSQL);

                List<ProductInfo> lst = new List<ProductInfo>();
                foreach (DataRow row in reader.Rows)
                {
                    ProductInfo tmpProduct = new ProductInfo();

                    HinhAnh tmpPic = new HinhAnh();
                    tmpPic.URL = "~//images//HinhAnhSanPham//" + row["Url"].ToString();
                    tmpProduct.Hinh_Anh = tmpPic;

                    LoaiSanPham tmpCategory = new LoaiSanPham();
                    tmpCategory.ID = int.Parse(row["idLoaiSanPham"].ToString());
                    tmpProduct.Loai_San_Pham = tmpCategory;

                    tmpProduct.TenSanPham = row["TenSanPham"].ToString();
                    tmpProduct.ID = int.Parse(row["id"].ToString());
                    tmpProduct.Gia = int.Parse(row["Gia"].ToString());

                    lst.Add(tmpProduct);
                }

                return lst;
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
            finally
            {
                reader.Clear();
            }
        }

        public List<ProductInfo> getTopNewProduct(int numProduct)
        {
            DataTable reader = null;
            try
            {
                string strSQL = "SELECT TOP " + numProduct.ToString() + " SAN_PHAM.id, SAN_PHAM.TenSanPham, SAN_PHAM.Gia, HINH_ANH.Url, SAN_PHAM.idLoaiSanPham FROM SAN_PHAM INNER JOIN HINH_ANH ON SAN_PHAM.id = HINH_ANH.idSanPham ORDER BY SAN_PHAM.NgayThemSP DESC";

                reader = _provider.ExecuteQueryDataTable(strSQL);

                List<ProductInfo> lst = new List<ProductInfo>();
                foreach (DataRow row in reader.Rows)
                {
                    ProductInfo tmpProduct = new ProductInfo();

                    HinhAnh tmpPic = new HinhAnh();
                    tmpPic.URL = "~//images//HinhAnhSanPham//" + row["Url"].ToString();
                    tmpProduct.Hinh_Anh = tmpPic;

                    LoaiSanPham tmpCategory = new LoaiSanPham();
                    tmpCategory.ID = int.Parse(row["idLoaiSanPham"].ToString());
                    tmpProduct.Loai_San_Pham = tmpCategory;

                    tmpProduct.TenSanPham = row["TenSanPham"].ToString();
                    tmpProduct.ID = int.Parse(row["id"].ToString());
                    tmpProduct.Gia = int.Parse(row["Gia"].ToString());

                    lst.Add(tmpProduct);
                }

                return lst;
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
            finally
            {
                reader.Clear();
            }
        }

        public List<ProductInfo> getTopFavoriteProduct(int numProduct)
        {
            DataTable reader = null;
            try
            {
                string strSQL = "SELECT TOP " + numProduct.ToString() + " SAN_PHAM.id, SAN_PHAM.TenSanPham, SAN_PHAM.Gia, HINH_ANH.Url, SAN_PHAM.idLoaiSanPham FROM SAN_PHAM INNER JOIN HINH_ANH ON SAN_PHAM.id = HINH_ANH.idSanPham ORDER BY SAN_PHAM.Diem DESC";

                reader = _provider.ExecuteQueryDataTable(strSQL);

                List<ProductInfo> lst = new List<ProductInfo>();
                foreach (DataRow row in reader.Rows)
                {
                    ProductInfo tmpProduct = new ProductInfo();

                    HinhAnh tmpPic = new HinhAnh();
                    tmpPic.URL = "~//images//HinhAnhSanPham//" + row["Url"].ToString();
                    tmpProduct.Hinh_Anh = tmpPic;

                    LoaiSanPham tmpCategory = new LoaiSanPham();
                    tmpCategory.ID = int.Parse(row["idLoaiSanPham"].ToString());
                    tmpProduct.Loai_San_Pham = tmpCategory;

                    tmpProduct.TenSanPham = row["TenSanPham"].ToString();
                    tmpProduct.ID = int.Parse(row["id"].ToString());
                    tmpProduct.Gia = int.Parse(row["Gia"].ToString());

                    lst.Add(tmpProduct);
                }

                return lst;
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
            finally
            {
                reader.Clear();
            }
        }

        public DataTable GetTableProductSaleOff()
        {
            DataTable reader = null;
            try
            {
                string strSQL = "SELECT KHUYEN_MAI.HinhThuc, KHUYEN_MAI.PhanTramGiam, SAN_PHAM.id, SAN_PHAM.TenSanPham, SAN_PHAM.Gia, HINH_ANH.id as idHinhAnh, HINH_ANH.Url, SAN_PHAM.idLoaiSanPham FROM SAN_PHAM INNER JOIN HINH_ANH ON SAN_PHAM.id = HINH_ANH.idSanPham INNER JOIN KHUYEN_MAI ON SAN_PHAM.idKhuyenMai = KHUYEN_MAI.id";

                reader = _provider.ExecuteQueryDataTable(strSQL);

                return reader;
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
        }

        public int GetProductIDByProductNameAndCateLogyAndManufacture(string sProductName,string sManufactureName,string sCatelogyName)
        {
            string sSql = "SELECT SAN_PHAM.ID FROM SAN_PHAM,NHA_SAN_XUAT, LOAI_SAN_PHAM WHERE SAN_PHAM.IDLOAISANPHAM = LOAI_SAN_PHAM.ID AND LOAI_SAN_PHAM.TENLOAISANPHAM ='" +sCatelogyName +
                "' AND SAN_PHAM.IDNSX = NHA_SAN_XUAT.ID AND NHA_SAN_XUAT.TENNSX = '" + sManufactureName +"' AND SAN_PHAM.TENSANPHAM = '" + sProductName+"'";
            
            int iID = -1;
            DataTable dt = _provider.ExecuteQueryDataTable(sSql);
            if(dt != null)
            {
                iID = int.Parse(dt.Rows[0][0].ToString());
            }
            return iID;
        }

        public List<string> GetListProductNameByMaFeNameAndCatName(string sManufactureName,string sCatelogyName)
        {
            List<string> lst = new List<string>();
            string sSql = "SELECT SAN_PHAM.TENSANPHAM FROM SAN_PHAM,NHA_SAN_XUAT, LOAI_SAN_PHAM WHERE SAN_PHAM.IDLOAISANPHAM = LOAI_SAN_PHAM.ID AND LOAI_SAN_PHAM.TENLOAISANPHAM ='" +sCatelogyName +
                "' AND SAN_PHAM.IDNSX = NHA_SAN_XUAT.ID AND NHA_SAN_XUAT.TENNSX = '" + sManufactureName +"'";
            DataTable dt = _provider.ExecuteQueryDataTable(sSql);
            if(dt != null)
            {
                int icount = dt.Rows.Count;
                for (int i = 0; i < icount;i++ )
                {
                    string s = dt.Rows[i][0].ToString();
                    lst.Add(s);
                }
            }
            return lst;
        }
                
        public ProductInfo getProductDetail(string sProductId, string sCategoryId, out DataTable dtSpecificInformation)        
        {
            ProductInfo product = new ProductInfo();
            try
            {
                //get basic information
                string strSQL = "SELECT HINH_ANH.id as idHinhAnh, HINH_ANH.idSanPham, HINH_ANH.Url, SAN_PHAM.TenSanPham, SAN_PHAM.Gia, SAN_PHAM.id, SAN_PHAM.idLoaiSanPham, LOAI_SAN_PHAM.TenLoaiSanPham FROM HINH_ANH INNER JOIN SAN_PHAM ON HINH_ANH.idSanPham = SAN_PHAM.id INNER JOIN LOAI_SAN_PHAM ON SAN_PHAM.idLoaiSanPham = LOAI_SAN_PHAM.id WHERE SAN_PHAM.id = " + sProductId;
                                
                dtSpecificInformation = _provider.ExecuteQueryDataTable(strSQL);
                foreach (DataRow row in dtSpecificInformation.Rows)
                {
                    product.TenSanPham = row["TenSanPham"].ToString();
                    product.Gia = int.Parse(row["Gia"].ToString());
                    product.Hinh_Anh = new HinhAnh();
                    product.Hinh_Anh.URL = "~//images//HinhAnhSanPham//" + row["Url"].ToString();

                    product.Loai_San_Pham = new LoaiSanPham();
                    product.Loai_San_Pham.TenLoaiSanPham = row["TenLoaiSanPham"].ToString();
                    product.Loai_San_Pham.ID = int.Parse(row["idLoaiSanPham"].ToString());
                    product.Hinh_Anh.ID = int.Parse(row["idHinhAnh"].ToString());
                }

                
                //get specific information     
                string sql = string.Empty;
                switch (product.Loai_San_Pham.TenLoaiSanPham)
                {
                    case "CPU":
                        sql = "SELECT CPU.Socket, CPU.Bus, CPU.Cache, CPU.Speed FROM CPU WHERE CPU.id = " + sProductId;
                        dtSpecificInformation = _provider.ExecuteQueryDataTable(sql);
                        break;

                    case "RAM":
                        sql = "SELECT RAM.Bus, RAM.DungLuong FROM RAM WHERE RAM.id = " + sProductId;
                        dtSpecificInformation = _provider.ExecuteQueryDataTable(sql);
                        break;

                    case "SOUNDCARD":
                        sql = "SELECT SOUND_CARD.CongSuat, SOUND_CARD.Ex FROM SOUND_CARD WHERE SOUND_CARD.id = " + sProductId;
                        dtSpecificInformation = _provider.ExecuteQueryDataTable(sql);
                        break;

                    case "MONITOR":
                        sql = "SELECT MONITOR.DoPhanGiai, MONITOR.DoTuongPhan, MONITOR.ThoiGianDapUng, MONITOR.CongGiaoTiep FROM MONITOR WHERE MONITOR.id = " + sProductId;
                        dtSpecificInformation = _provider.ExecuteQueryDataTable(sql);
                        break;

                    case "CASE":
                        sql = "SELECT CASE.KichThuoc, CASE.VatLieu, CASE.HoTro, CASE.Nguon, CASE.CongGiaoTiep, CASE.KhongGianChua, CASE.MoRong, CASE.TanNhiet FROM CASE WHERE CASE.id = " + sProductId;
                        dtSpecificInformation = _provider.ExecuteQueryDataTable(sql);
                        break;

                    case "KEYBOARD":
                        sql = "SELECT KEYBOARD.CongGiaoTiep, KEYBOARD.Mau FROM KEYBOARD WHERE KEYBOARD.id = " + sProductId;
                        dtSpecificInformation = _provider.ExecuteQueryDataTable(sql);
                        break;

                    case "SPEAKER":
                        sql = "SELECT SPEAKER.CongSuat, SPEAKER.Chuan FROM SPEAKER WHERE SPEAKER.id = " + sProductId;
                        dtSpecificInformation = _provider.ExecuteQueryDataTable(sql);
                        break;
                    case "VGA":
                        sql = "SELECT VGA.Engine, VGA.VideoMemory, VGA.InterfaceMemory, VGA.CongGiaoTiep FROM VGA WHERE VGA.id = " + sProductId;
                        dtSpecificInformation = _provider.ExecuteQueryDataTable(sql);
                        break;
                    case "POWER":
                        sql = "SELECT POWER.CongSuat, POWER.Quat, POWER.Connector FROM POWER WHERE POWER.id = " + sProductId;
                        dtSpecificInformation = _provider.ExecuteQueryDataTable(sql);
                        break;
                    case "CD_DVD":
                        sql = "SELECT CD_DVD.TocDo, CD_DVD.CongGiaoTiep FROM CD_DVD WHERE CD_DVD.id = " + sProductId;
                        dtSpecificInformation = _provider.ExecuteQueryDataTable(sql);
                        break;
                    case "PIN":
                        sql = "SELECT PIN.Cell, PIN.CongNghe FROM PIN WHERE PIN.id = " + sProductId;
                        dtSpecificInformation = _provider.ExecuteQueryDataTable(sql);
                        break;
                    case "HDD":
                        sql = "SELECT HDD.DungLuong, HDD.Chuan, HDD.SoVong FROM HDD WHERE HDD.id = " + sProductId;
                        dtSpecificInformation = _provider.ExecuteQueryDataTable(sql);
                        break;
                    case "MAINBOARD":
                        sql = "SELECT MAINBOARD.Cpu, MAINBOARD.Ram, MAINBOARD.Chipset, MAINBOARD.Audio, MAINBOARD.Video, MAINBOARD.form_factor, MAINBOARD.io_control, MAINBOARD.Network, MAINBOARD.Interface, MAINBOARD.Expansion FROM MAINBOARD WHERE MAINBOARD.id = " + sProductId;
                        dtSpecificInformation = _provider.ExecuteQueryDataTable(sql);
                        break;
                    case "MOUSE":
                        sql = "SELECT MOUSE.CongGiaoTiep, MOUSE.Mau FROM MOUSE WHERE MOUSE.id = " + sProductId;
                        dtSpecificInformation = _provider.ExecuteQueryDataTable(sql);
                        break;
                    case "OS":
                        //sql = "SELECT MOUSE.CongGiaoTiep, MOUSE.Mau FROM MOUSE WHERE MOUSE.id = " + sProductId;
                        //dtSpecificInformation = _provider.ExecuteQueryDataTable(sql);
                        break;
                    case "DESKTOP":
                        /*
                         * id, idCpu, idSoundcard, idRam, idHdd, idCdDvd, idVga, idCase, idHdh??, idKeyboard, idMouse, idMainboard, idMonitor, idPower, idSpearker, LaSanPhamCustom
                         * 
                         */
                        sql = "SELECT DESKTOP.id, " + 
                                "CPU.Socket, CPU.Bus, CPU.Cache, CPU.Speed, " + 
                                "SOUND_CARD.CongSuat, SOUND_CARD.Ex, " + 
                                "RAM.DungLuong, " + 
                                "HDD.DungLuong AS Expr1, " +
                                "CD_DVD.TocDo, CD_DVD.CongGiaoTiep, " + 
                                "VGA.Engine, VGA.VideoMemory, VGA.InterfaceMemory, VGA.CongGiaoTiep AS Expr9, " +
                                "KEYBOARD.CongGiaoTiep AS Expr11, KEYBOARD.Mau, " + 
                                "MOUSE.CongGiaoTiep AS Expr13, MOUSE.Mau AS Expr14, " + 
                                "MAINBOARD.Cpu, MAINBOARD.Ram, MAINBOARD.Chipset, MAINBOARD.Audio, MAINBOARD.Video, MAINBOARD.form_factor, MAINBOARD.io_control, MAINBOARD.Network, MAINBOARD.Interface, MAINBOARD.Expansion, MONITOR.DoPhanGiai, MONITOR.DoTuongPhan, MONITOR.ThoiGianDapUng, " + 
                                "MONITOR.CongGiaoTiep AS Expr17, " + 
                                "POWER.CongSuat AS Expr19, POWER.Quat, POWER.Connector, " + 
                                "SPEAKER.CongSuat AS Expr21, SPEAKER.Chuan AS Expr22 " +
                             "FROM DESKTOP LEFT OUTER JOIN " + 
                                "CPU ON DESKTOP.idCpu = CPU.id LEFT OUTER JOIN " +
                                "SOUND_CARD ON DESKTOP.idSoundcard = SOUND_CARD.id LEFT OUTER JOIN " +
                                "RAM ON DESKTOP.idRam = RAM.id LEFT OUTER JOIN " +
                                "HDD ON DESKTOP.idHdd = HDD.id LEFT OUTER JOIN " +
                                "CD_DVD ON DESKTOP.idCdDvd = CD_DVD.id LEFT OUTER JOIN " + 
                                "VGA ON DESKTOP.idVga = VGA.id LEFT OUTER JOIN " +
                                "KEYBOARD ON DESKTOP.idKeyboard = KEYBOARD.id LEFT OUTER JOIN " +
                                "MOUSE ON DESKTOP.idMouse = MOUSE.id LEFT OUTER JOIN " + 
                                "MAINBOARD ON DESKTOP.idMainboard = MAINBOARD.id LEFT OUTER JOIN " +
                                "MONITOR ON DESKTOP.idMonitor = MONITOR.id LEFT OUTER JOIN " +
                                "POWER ON DESKTOP.idPower = POWER.id LEFT OUTER JOIN " +
                                "SPEAKER ON DESKTOP.idSpearker = SPEAKER.id " +
                             "WHERE (DESKTOP.LaSanPhamCustom = 0 AND DESKTOP.id = " + sProductId + ")";
                                                
                        dtSpecificInformation = _provider.ExecuteQueryDataTable(sql);
                        break;
                    case "LAPTOP":                        
                        sql = "SELECT LAPTOP.id, LAPTOP.ScreenSize, LAPTOP.KhoiLuong, " +
                            "CPU.Bus, CPU.Cache, CPU.Speed, " +
                            "SOUND_CARD.CongSuat, SOUND_CARD.Ex, " +
                            "RAM.Bus AS Expr4, RAM.DungLuong, " +
                            "HDD.DungLuong AS Expr6, " +
                            "CD_DVD.TocDo, CD_DVD.CongGiaoTiep, " +
                            "PIN.Cell, PIN.CongNghe, " +
                            "VGA.Engine, VGA.VideoMemory, VGA.InterfaceMemory, VGA.CongGiaoTiep AS Expr10, " +
                            "MAINBOARD.Cpu, MAINBOARD.Ram, MAINBOARD.Chipset, MAINBOARD.Audio, MAINBOARD.Video, MAINBOARD.form_factor, MAINBOARD.io_control, MAINBOARD.Network, MAINBOARD.Interface, MAINBOARD.Expansion, " +
                            "CPU.Socket " +
                        "FROM LAPTOP LEFT OUTER JOIN " +
                            "CPU ON LAPTOP.idCpu = CPU.id LEFT OUTER JOIN " +
                            "SOUND_CARD ON LAPTOP.idSoundCard = SOUND_CARD.id LEFT OUTER JOIN " +
                            "RAM ON LAPTOP.idRam = RAM.id LEFT OUTER JOIN " +
                            "HDD ON LAPTOP.idHdd = HDD.id LEFT OUTER JOIN " +
                            "CD_DVD ON LAPTOP.idCdDvd = CD_DVD.id LEFT OUTER JOIN " +
                            "PIN ON LAPTOP.idPin = PIN.id LEFT OUTER JOIN " +
                            "VGA ON LAPTOP.idVga = VGA.id LEFT OUTER JOIN " +
                            "MAINBOARD ON LAPTOP.idMainboard = MAINBOARD.id " +
                        "WHERE (LAPTOP.LaSanPhamCustom = 0 AND LAPTOP.id = " + sProductId + ")";
                        
                        dtSpecificInformation = _provider.ExecuteQueryDataTable(sql);
                        break;
                }

                return product;
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }            
        }

        public DataTable getTableProductByCategory(int categoryId)
        {
            DataTable table = null;
            try
            {
                string sql = "SELECT SAN_PHAM.id, HINH_ANH.id as idHinhAnh, HINH_ANH.Url, SAN_PHAM.TenSanPham, SAN_PHAM.MoTa, SAN_PHAM.Gia, SAN_PHAM.idLoaiSanPham FROM HINH_ANH INNER JOIN SAN_PHAM ON HINH_ANH.idSanPham = SAN_PHAM.id WHERE (SAN_PHAM.idLoaiSanPham = " + categoryId.ToString() + ")";
                table = _provider.ExecuteQueryDataTable(sql);
                return table;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public int GetIdHinhAnhFromIdSanPham(int idSanPham)
        {
            DataTable table = null;
            try
            {
                string strSQL = String.Format("Select id From HINH_ANH Where idSanPham = {0}",idSanPham);

                table = _provider.ExecuteQueryDataTable(strSQL);

                return Convert.ToInt32(table.Rows[0]["id"].ToString());
            }
            catch
            {
                return -1;
            }
        }

        public String GetLoaiSanPhamFromId(int idSanPham)
        {
            DataTable table = null;
            try
            {
                string strSQL = String.Format("Select TenLoaiSanPham From Loai_San_Pham, San_Pham Where San_Pham.idLoaiSanPham = Loai_San_Pham.id and San_Pham.id = {0}", idSanPham);

                table = _provider.ExecuteQueryDataTable(strSQL);

                return table.Rows[0]["TenLoaiSanPham"].ToString();
            }
            catch
            {
                return "";
            }
        }

        public bool DeleteProductWithID(int idSanPham)
        {
            try
            {
                int idHinhAnh = GetIdHinhAnhFromIdSanPham(idSanPham);
                if (idHinhAnh == -1)
                    return false;
                else
                {
                    String strSQL = String.Format("Delete From CHI_TIET_HOA_DON Where idHinhAnh = {0}", idHinhAnh);
                    _provider.ExecuteNonQuery(strSQL); 
                   
                    strSQL = String.Format("Delete From CHI_TIET_GIO_HANG Where idHinhAnh = {0}", idHinhAnh);
                    _provider.ExecuteNonQuery(strSQL); 
                        
                    strSQL = String.Format("Delete From HINH_ANH Where idSanPham = {0}", idSanPham);
                    _provider.ExecuteNonQuery(strSQL);

                    strSQL = String.Format("Delete From DANH_GIA Where idSanPham = {0}", idSanPham);
                    _provider.ExecuteNonQuery(strSQL); 

                    strSQL = String.Format("Delete From SAN_PHAM_UA_THICH Where idSanPham = {0}", idSanPham);
                    _provider.ExecuteNonQuery(strSQL);

                    String strLoaiSanPham = GetLoaiSanPhamFromId(idSanPham);

                    switch (strLoaiSanPham)
                    {
                        case "SOUNDCARD":
                            strSQL = String.Format("Delete From SOUND_CARD Where id = {0}", idSanPham);
                            break;

                        default:
                            strSQL = String.Format("Delete From " + strLoaiSanPham + " Where id = {0}", idSanPham);
                            break;
                    }

                    _provider.ExecuteNonQuery(strSQL);

                    strSQL = String.Format("Delete From SAN_PHAM Where id = {0}", idSanPham);
                    _provider.ExecuteNonQuery(strSQL);
                    
                    return true;
                }
            }
            catch
            {
                return false;
            }
        }

        public ProductInfo getProductById(int idProduct)
        {
            DataTable table = null;
            try
            {                
                string sql = "SELECT ID, TenSanPham, Gia, Diem, MoTa, SoLuongTon, NgayThemSP, BaoHanh, LoaiLinhKien FROM SAN_PHAM WHERE SAN_PHAM.id = " + idProduct.ToString();
                table = _provider.ExecuteQueryDataTable(sql);

                ProductInfo productInfo = new ProductInfo();
                foreach (DataRow row in table.Rows)
                {
                    productInfo.ID = Convert.ToInt32(row["ID"].ToString());
                    productInfo.TenSanPham = row["TenSanPham"].ToString();
                    productInfo.NgayThemSP = DateTime.Parse(row["NgayThemSP"].ToString());
                    productInfo.MoTa = row["MoTa"].ToString();
                    productInfo.SoLuongTon = int.Parse(row["SoLuongTon"].ToString());
                    productInfo.BaoHanh = row["BaoHanh"].ToString();
                    productInfo.Gia = Convert.ToInt32(row["Gia"].ToString());
                    productInfo.LoaiLinhKien = int.Parse(row["LoaiLinhKien"].ToString());
                    productInfo.Diem = Convert.ToInt32(row["Diem"].ToString());                   
                }

                sql = "SELECT TenNhaPhanPhoi FROM NHA_PHAN_PHOI, SAN_PHAM WHERE SAN_PHAM.idNhaPhanPhoi = NHA_PHAN_PHOI.id AND SAN_PHAM.id = " + idProduct.ToString();
                table = _provider.ExecuteQueryDataTable(sql);
                productInfo.Nha_Phan_Phoi = new NhaPhanPhoi();
                foreach (DataRow row in table.Rows)
                {                    
                    productInfo.Nha_Phan_Phoi.TenNhanPhanPhoi = row["TenNhaPhanPhoi"].ToString();
                }

                sql = "SELECT URL FROM SAN_PHAM, HINH_ANH WHERE SAN_PHAM.id = HINH_ANH.idSanPham AND SAN_PHAM.id =" + idProduct.ToString();
                table = _provider.ExecuteQueryDataTable(sql);
                productInfo.Hinh_Anh = new HinhAnh();
                foreach (DataRow row in table.Rows)
                {
                    productInfo.Hinh_Anh.URL = row["URL"].ToString();
                }

                sql = "SELECT LOAI_SAN_PHAM.id,TenLoaiSanPham from LOAI_SAN_PHAM,SAN_PHAM WHERE SAN_PHAM.IDLOAISANPHAM = LOAI_SAN_PHAM.ID AND SAN_PHAM.ID =" + idProduct.ToString();
                table = _provider.ExecuteQueryDataTable(sql);
                productInfo.Loai_San_Pham = new LoaiSanPham();
                foreach (DataRow row in table.Rows)
                {
                    productInfo.Loai_San_Pham.ID = int.Parse(row["ID"].ToString());
                    productInfo.Loai_San_Pham.TenLoaiSanPham = row["TenLoaiSanPham"].ToString();
                }

                
                sql = "SELECT TenNSX, QuocGia FROM SAN_PHAM, NHA_SAN_XUAT WHERE NHA_SAN_XUAT.id = SAN_PHAM.idNSX AND SAN_PHAM.id = " + idProduct.ToString();
                table = _provider.ExecuteQueryDataTable(sql);
                productInfo.NSX = new NhaSanXuat();
                foreach (DataRow row in table.Rows)
                {                    
                    productInfo.NSX.TenNSX = row["TenNSX"].ToString();
                    productInfo.NSX.QuocGia = row["QuocGia"].ToString();
                }

                sql = "SELECT HinhThuc, PhanTramGiam FROM KHUYEN_MAI, SAN_PHAM WHERE SAN_PHAM.idKhuyenMai = KHUYEN_MAI.id AND SAN_PHAM.id = " + idProduct.ToString();
                table = _provider.ExecuteQueryDataTable(sql);
                productInfo.Khuyen_Mai = new KhuyenMai();
                foreach (DataRow row in table.Rows)
                {                   
                    productInfo.Khuyen_Mai.HinhThuc = row["HinhThuc"].ToString();
                    productInfo.Khuyen_Mai.PhanTramGiam = int.Parse(row["PhanTramGiam"].ToString());
                }                               
                
                return productInfo;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (table != null)
                    table.Clear();
            }
        }

        public bool AddFavoriteProduct(int idUser, int idSanPham)
        {            
            try
            {
                int id = this.GetMaxIDProductFavourite();
                if (id != -1)
                {
                    string strSQL = string.Format("INSERT INTO SAN_PHAM_UA_THICH(id, idSanPham, idNguoiDung) VALUES({0},{1},{2})",id + 1, idSanPham, idUser);

                    return (_provider.ExecuteNonQuery(strSQL) > 0);
                }
                else
                {
                    return false;
                }
            }
            catch
            {
                return false;
            }
        }

        private int GetMaxIDProductFavourite()
        {
            try
            {
                string strSQL = "SELECT TOP 1 id FROM SAN_PHAM_UA_THICH ORDER BY id DESC";

                DataTable table = _provider.ExecuteQueryDataTable(strSQL);
                if (table != null && table.Rows.Count > 0)
                {
                    return int.Parse(table.Rows[0]["id"].ToString());
                }
                else
                    return -1;
            }
            catch
            {
                return -1;
            }
        }

        public bool CheckProductFavourite(int idUser, int idSanPham)
        {            
            try
            {
                string strSQL = string.Format("SELECT * FROM SAN_PHAM_UA_THICH WHERE idSanPham = {0} AND idNguoiDung = {1}", idSanPham, idUser);

                return (_provider.ExecuteQueryDataTable(strSQL).Rows.Count > 0);
            }
            catch
            {
                return false;
            }
        }
    }
}